--3.2
--第四层sql
select 
	id,
	--组内第一次浏览时间
	first_time,
	--组内最后一次浏览时间
	last_time,
	--时间差
	(unix_timestamp(last_time,'yyyy/MM/dd HH:mm') - unix_timestamp(first_time,'yyyy/MM/dd HH:mm'))/60 as duration_time2,
	--步长
	max(num)-min(num2)+1 as count
from(
	--第三层sql：
	select 
		id,
		--同个id内部不同浏览时间的组的编号
		groupid,
		--不同浏览时间组内第一次浏览时间
		first_value(prev_time) over(partition by id,groupid order by dt rows between unbounded preceding and unbounded following) as first_time,
		--不同浏览时间组内最后一次浏览时间
		last_value(dt) over(partition by id,groupid order by dt rows between unbounded preceding and unbounded following) as last_time,
		--时长
		duration_time,
		--全部记录编码
		num,
		--小于30分钟记录的新新编号
		num2
	from(
		--第二层sql：
		select id,dt,prev_time,
			--计算每次浏览的时长
			(unix_timestamp(dt,'yyyy/MM/dd HH:mm') - unix_timestamp(prev_time,'yyyy/MM/dd HH:mm'))/60 as duration_time,
			--全部记录的编号
			num,
			--给删掉大于30分钟记录的数据重新编号
			row_number() over(partition by id order by dt,num) as num2,
			--上次编号-这次编号。值不同则为不同浏览时间
			num - row_number() over(partition by id order by dt,num) as groupid
		from(
			--第一层sql:
			--开窗函数得到上一次浏览时间，按id分组给组内给记录编号
			select id,
				dt,
				nvl(lag(dt) over(partition by id order by dt),dt) as prev_time,
				row_number() over(partition by id order by dt) as num
			from t3)tmp
			--删除大于30分钟的记录
		where (unix_timestamp(dt,'yyyy/MM/dd HH:mm') - unix_timestamp(prev_time,'yyyy/MM/dd HH:mm'))/60 < 30)tmp2
	)tmp3
group by id,groupid,first_time,last_time;